package com.learning.android.currencyconvertor;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class CurrencyDBAdapter {
	//Constant Variables for Key Columns
	public static final String KEY_ID = "_id";
	public static final String KEY_FROM_CURR_ID = "from_curr_id";
	public static final String KEY_TO_CURR_ID = "to_curr_id";
	public static final String KEY_FROM_CURR = "from_curr";
	public static final String KEY_TO_CURR = "to_curr";
	public static final String KEY_FROM_CURR_AMT = "from_curr_amt";
	public static final String KEY_TO_CURR_AMT = "to_curr_amt";
	public static final String KEY_RATE_ADV = "rate_adv";
	public static final String KEY_RATE_REV = "rate_rev";
	public static final String KEY_DIRECTION = "direction";
	public static final String KEY_SELECTION = "selection";
	public static final String KEY_FAVOURITE = "favourite";
	public static final String KEY_CURRENCY = "currency";
	public static final String KEY_CURR_DESC = "curr_desc";
	public static final String KEY_FLAG = "flag";
	
	//Constant Names for tables
	public static final String TBL_XCHGRATE = "xchgrates";
	public static final String TBL_CURR = "currencies";
	
	/* Currency Selected Mode
	 * 0 --> Only for previous selected currencies
	 * -1 --> Select All currencies
	 */
	public static int[] SELECTION_MODE = {0,-1};
	

	//private static final String TAG = "CurrencyDBAdapter";
	private DatabaseHelper mDbHelper;
	private static SQLiteDatabase mDb;
	
	//Create Xchge Rate Table, this table is to store all the Xchg Rate basing on from_currency to to_currency
	private static final String DATA_CREATE_XCHGRATE = 
			"create table xchgrates(_id integer primary key autoincrement," +
			"from_curr_id integer not null," +
			"to_curr_id integer not null," +
			"from_curr text not null," +
			"to_curr text not null," +
			"from_curr_amt double,"+
			"to_curr_amt double,"+
			"rate_adv double," +
			"rate_rev double," +
			"direction boolean," +
			"selection integer," +
			"FOREIGN KEY(from_curr_id) REFERENCES currencies(_id),"+
			"FOREIGN KEY(to_curr_id) REFERENCES currencies(_id)"+
			")";
	
	//Create Currencies Table, this table is to store all the currencies that can be selected to the exchange rate
	private static final String DATA_CREATE_CURRENCY = 
		"create table currencies(_id integer primary key autoincrement," +
		"currency text not null," +
		"favourite boolean," +
		"curr_desc text," +
		"flag int)";
	
	private static final String DATABASE_NAME = "data";
	private static final String CURRENCIES_TABLE_NAME = "currencies";
	private static final String XCHGRATES_TABLE_NAME = "xchgrates";
	private static final int DATABASE_VERSION = 16;
	
	private static  Context mCtx;
	
	private static class DatabaseHelper extends SQLiteOpenHelper{

		  DatabaseHelper(Context context) {
	            super(context, DATABASE_NAME, null, DATABASE_VERSION);
	        }

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			
			db.execSQL(DATA_CREATE_CURRENCY);
			db.execSQL(DATA_CREATE_XCHGRATE);
			//fillCurrency(db);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			System.out.println("updating database from version "+ oldVersion+" to "+ newVersion + " , which will destroy all the old data");
			 db.execSQL("DROP TABLE IF EXISTS xchgrates");
			 db.execSQL("DROP TABLE IF EXISTS currencies");
	         onCreate(db);
	         fillCurrency(db);	         
		}
	
	    /**
	     * @param db
	     */
	    public  void fillCurrency(SQLiteDatabase db) {

			// TODO Auto-generated method stub
	    	String[] currencies = mCtx.getResources().getStringArray(R.array.currencies);
	    	int [] flags  = new int[] {0x7f02000a,0x7f020006,0x7f020000,0x7f020005,0x7f020008,
	    			                   0x7f020009,0x7f020002,0x7f020003,0x7f020007};
	    	
	    	for(int i = 0;i<currencies.length;i++)
	    	{
	    		addCurrency(db,currencies[i], flags[i],  true, null);
	    	}
	    	
	    	loadXchgRates(db);
		}
		
	    /**
	     * Add New Currency to table currencies
	     */
	    public  long addCurrency(SQLiteDatabase db,String currency,  int flag, boolean favourite, String desc)
	    {
	    	ContentValues args = new ContentValues();
	    	args.put(KEY_CURRENCY, currency);
	    	args.put(KEY_FAVOURITE, favourite);
	    	args.put(KEY_FLAG, flag);
	    	args.put(KEY_CURR_DESC, desc);
	    	
			return db.insert(CURRENCIES_TABLE_NAME, null, args);
	    }
	    
	    /**
	     * Load currencies stored in currencies to xchgrates Table
	     */
	    
	    public  void loadXchgRates(SQLiteDatabase db)
	    {
			String load = "insert into "+XCHGRATES_TABLE_NAME+" (from_curr_id, to_curr_id," +
					                             "from_curr,to_curr,direction, from_curr_amt,to_curr_amt,selection) " +
					     "select a._id from_curr_id," +
					     "b._id to_curr_id," +
					     "a.currency from_curr," +
					     "b.currency to_curr," +
					     "\"false\"," +
					     "\"1\"," +
					     "\"1\", " +
					     "\"0\" " +
					     "from currencies a, currencies b " +
					     "where a._id <> b._id and b._id < a._id";
			String select = "update xchgrates set selection = _id where _id < 6 ";
			try {
			db.execSQL(load);
			db.execSQL(select);
			} 
			catch (Exception e)
			{
			System.out.println("Exception: "+e.toString());
			}
	    }
	}
	
    
    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public CurrencyDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }
    
    
    /**
     * Loading the selected currency pairs
     * @param select 
     *   0 only for previously selected currencies ;
     *   -1 for all currencies in the table;
     */
    public Cursor selectedCurrencies(int select)
    {
    	String sql = "select a._id, a.from_curr_id, a.from_curr, " +
    			      "a.to_curr_id, a.to_curr, a.direction, a.selection,froc.flag from_curr_flag, " +
    			      "toc.flag to_curr_flag, a.rate_adv, a.rate_rev, a.from_curr_amt, a.to_curr_amt " +
    			      "from "+XCHGRATES_TABLE_NAME+" a, currencies froc, currencies toc " +
    			      "where a.selection > "+select+" and a.from_curr_id = froc._id " +
    			      "and a.to_curr_id = toc._id order by a.selection";
    	
    	return mDb.rawQuery(sql, null);
    	//return null;
    }
    
    /**
     * fetchCurrencyConfig Select Currency Configuration Information
     * 
     * @param favourite true -> only for Currencies Marked as favourite
     *                  false -> All the Currencies
     * @return Cursor contains currencies configuration information
     */
    public Cursor fetchCurrencyConfig(int favourite)
    {
    	return  mDb.query(CURRENCIES_TABLE_NAME, new String[]{ KEY_ID,KEY_CURRENCY,KEY_CURR_DESC,KEY_FAVOURITE,KEY_FLAG}, KEY_FAVOURITE+">?", new String[]{favourite+""}, null, null, KEY_ID);
    }
    
    /*
     * Update Currencies Information in xchgrate Table basing on row_id
     */
    public synchronized int updateXchgRate (int rowId, float rateAdv, float rateRev, int selection, int direction, double fromCurrAmt, double toCurrAmt)
    {
    	ContentValues xchgRecord = new ContentValues();
    	
    	if(rateAdv!=0)
    	xchgRecord.put(KEY_RATE_ADV, rateAdv);
    	if(rateRev!=0)
    	xchgRecord.put(KEY_RATE_REV, rateRev);
    	
    	if(selection >=0)
    	xchgRecord.put(KEY_SELECTION, selection);
    	
    	if(direction == 0 || direction==1)
    	xchgRecord.put(KEY_DIRECTION, direction);
    	
    	if(fromCurrAmt!=0)
    	xchgRecord.put(KEY_FROM_CURR_AMT, fromCurrAmt);
    	if(toCurrAmt!=0)
    	xchgRecord.put(KEY_TO_CURR_AMT, toCurrAmt);
    	//mDb.update(TBL_XCHGRATE, values, whereClause, whereArgs)
    	int rn =0;
    	try{
    		//mDb.setLockingEnabled(true);
    		rn= mDb.update(TBL_XCHGRATE, xchgRecord, "_id="+rowId, null);
    		//mDb.setLockingEnabled(false);
    	} catch(Exception e)
    	{
    		System.out.println("Database update excpetion "+e.toString());
    	}
    	return rn;
    }
    
     
    /**
     * To Get latest exchange rate from data base and populate them in Array List
     * @param currencyXchgRatePair
     * @return CurrencyXchgRatePair
     */
    public CurrencyXchgRatePair getNewCurrencyPair(CurrencyXchgRatePair currencyXchgRatePair)
    {
    	
    	String sql = "Select _id,from_curr_amt,to_curr_amt,rate_adv,rate_rev,from_curr_id, to_curr_id, direction, selection " +
    			     "from xchgrates where from_curr_id in ("+currencyXchgRatePair.getFromCurrID()+", "+currencyXchgRatePair.getToCurrID()+") " +
    			     "and to_curr_id in ("+currencyXchgRatePair.getFromCurrID()+", "+currencyXchgRatePair.getToCurrID()+");";
    	
    	if(updateXchgRate(currencyXchgRatePair.getDbRowID(), 0, 0, 0, 0, 1, 1)==0)
    	{
    		return null;
    	}
    	
    	//Get CurrencyXchgRatePair basing on From Currency ID & To Currency ID
    	
    	Cursor currXchgRateCursor = mDb.rawQuery(sql, null);
    	
    	currXchgRateCursor.moveToFirst();
    	
    	currencyXchgRatePair.setDirection(!currencyXchgRatePair.isDirection()?(!(currXchgRateCursor.getInt(currXchgRateCursor.getColumnIndex(KEY_FROM_CURR_ID))==currencyXchgRatePair.getFromCurrID())):
    		!(!(currXchgRateCursor.getInt(currXchgRateCursor.getColumnIndex(KEY_FROM_CURR_ID))==currencyXchgRatePair.getFromCurrID())));
    	
    	currencyXchgRatePair.setDbRowID(currXchgRateCursor.getInt(currXchgRateCursor.getColumnIndex(CurrencyDBAdapter.KEY_ID)));
    	currencyXchgRatePair.setFromCurrID(currXchgRateCursor.getInt(currXchgRateCursor.getColumnIndex(CurrencyDBAdapter.KEY_FROM_CURR_ID)));
    	currencyXchgRatePair.setToCurrID(currXchgRateCursor.getInt(currXchgRateCursor.getColumnIndex(CurrencyDBAdapter.KEY_TO_CURR_ID)));
    	currencyXchgRatePair.setRateADV(currXchgRateCursor.getFloat(currXchgRateCursor.getColumnIndex(CurrencyDBAdapter.KEY_RATE_ADV)));
    	currencyXchgRatePair.setRateREV(currXchgRateCursor.getFloat(currXchgRateCursor.getColumnIndex(CurrencyDBAdapter.KEY_RATE_REV)));
    	
    	
    	//Update Column of Direction and Selection from Database
//    	xchgRateRecord.clear();
//    	xchgRateRecord.put(KEY_SELECTION,currencyXchgRatePair.getSelection());
//    	xchgRateRecord.put(KEY_DIRECTION, currencyXchgRatePair.isDirection());
//    	if(mDb.update(XCHGRATES_TABLE_NAME, xchgRateRecord, KEY_ID+"="+currencyXchgRatePair.getDbRowID(), null)==0)
//    	{
//    		return null;
//    	}
    	if(updateXchgRate(currencyXchgRatePair.getDbRowID(),0,0,currencyXchgRatePair.getSelection(),currencyXchgRatePair.isDirection()==false?0:1,0,0)==0)
    	{
    		return null;
    	}
    	
		return currencyXchgRatePair;
    }
    
   
    /**
     * Open the notes database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         Initialisation call)
     * @throws SQLException if the database could be neither opened or created
     */
    public CurrencyDBAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }
    
    public void close() {
        mDbHelper.close();
    }
    

}
